SQL*Text is simply a home-grown SQL script (thanks to Adam Dickman) that queries Oracle’s data dictionary views to show all SQLs running for a given User ID, Server PID, or Client PID.
Download SQL*Text, and run it from SQL*Plus as follows:
SQL> @st
Enter the users operating system Id.,
client process id, or database process id :
At the prompt above, enter the operating system User ID of the person currently running a long running job. Alternatively, enter the PID of the Oracle shadow process, or the PID of the client process. If you don’t know these, or don’t know how to find them out, it would be simpler just to supply the User ID.
SQL*Text will then list all of the SQLs running for that user (or PID), and provide some indication of what that SQL is doing. Eg:
O/SUser CPid SPid DbUser S WaitResn -------- ----- ----- -------- - -------- c949959 553:379 872789 CONQDEV I ClientSQLNet SQLText -------------------------------------------------------------------------------- commit
The output above is simplistic, but it shows that the session for user c949959 last ran a commit, and is currently in status ClientSQLNet: meaning that the database process is idle, and control has been handed back to the client process or program.
Common wait Reasons include:
For a detailed description of other wait reasons, see Oracle’s online documentation on the V$SESSION_WAIT view.
For those unimpressed by the green-screen technology of this tool, most of the information is also available in Oracle Enterprise Manager, however it is slower and harder to establish the Wait Reason.